﻿using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System;

namespace Utilities
{
    /// <summary>
    /// 提供Excel相关的操作
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 获得一个Excel的DataSet对象
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="Sheet">工作薄名称(为NULL或""则返回包含所有Sheet的DataSet)</param>
        /// <returns>DataSet</returns>
        public static DataSet getExcelDataSet(string FilePath, string Sheet)
        {
            const string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            const string SQL = "Select * FROM [{0}]";

            DataSet DS = new DataSet();
            OleDbConnection Conn = new OleDbConnection(string.Format(ConnStr, FilePath));
            try
            {

                Conn.Open();

                if (string.IsNullOrEmpty(Sheet))
                {
                    ArrayList AL = new ArrayList();
                    DataTable DT = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    foreach (DataRow DR in DT.Rows)
                    {
                        string SheetName = DR[2].ToString();
                        if (SheetName.EndsWith("$"))
                        {
                            AL.Add(DR[2]);
                        }
                    }
                    for (int i = 0; i < AL.Count; i++)
                    {
                        OleDbCommand Comm = new OleDbCommand(string.Format(SQL, AL[i].ToString()), Conn);
                        OleDbDataAdapter ODA = new OleDbDataAdapter(Comm);
                        ODA.Fill(DS, AL[i].ToString());
                    }
                }
                else
                {
                    OleDbCommand Comm = new OleDbCommand(string.Format(SQL, Sheet), Conn);
                    OleDbDataAdapter ODA = new OleDbDataAdapter(Comm);
                    ODA.Fill(DS, Sheet);
                }
                // Conn.Close();
            }
            catch (Exception e)
            {
                throw new Exception("将excel中指定sheet内容读入dataset出错！" + e.Message + " fileName: " + FilePath + " ; sheetName:" + Sheet);
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Dispose();
                    Conn.Close();
                }
            }

            return DS;
        }

        /// <summary>
        /// 根据二进制生成物理Excel文件并返回 绝对物理地址
        /// </summary>
        /// <param name="FileContent"></param>
        /// <returns></returns>
        public static string CreateExcel(string FolderPath, byte[] FileContent)
        {
            string filePath = string.Empty;
            if (FileContent.Length > 0)
            {
                FileStream stream;
                BinaryWriter writer;
                filePath = FolderPath + DateTime.Now.ToString("yyyyMMddHHmmssfff") + (new Random()).Next().ToString().Substring(0, 4) + ".xls";
                while (System.IO.File.Exists(filePath))
                {
                    filePath = FolderPath + DateTime.Now.ToString("yyyyMMddHHmmssfff") + (new Random()).Next().ToString().Substring(0, 4) + ".xls";
                }
                stream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write);
                writer = new BinaryWriter(stream);
                writer.Write(FileContent, 0, FileContent.Length);
                writer.Flush();
                writer.Close();
                stream.Close();
            }
            return filePath;
        }
    }
}
